import pandas as pd
import statsmodels.api as sm
import matplotlib.pyplot as plt
import numpy as np

def readData():
    data = pd.read_excel("D:\\2020data01.xlsx", sheet_name='initial')
    data = data[4:]
    return data

def generateIndex(data):
    # data.iloc[4,0]='1111-03-31 00:00:00'
    columns=data.columns.tolist()
    return data[columns[0]],columns[1:]


def HPFilter(data,Timeindex,Allindex):
        # col=1658


    for col in range(2159):
        data.set_index(Timeindex, inplace=True)

        res = data[[Allindex[col]]]
        NotNull = data[data[Allindex[col]].notnull()]

        cycle, trend = sm.tsa.filters.hpfilter(NotNull[Allindex[col]], 1600)


        res.insert(res.shape[1], 'cycle', cycle)
        res.insert(res.shape[1], 'trend', trend)

        mean = res.mean()
        std = res.std()
        resNew=res.copy()

        for i in range(72):

            if res.loc[Timeindex[i+4], "cycle"] > 1.5 * std[1]:
                resNew.loc[Timeindex[i+4], "cycle"] = 1.5 * std[1]
            elif res.loc[Timeindex[i+4], "cycle"] < -1.5 * std[1]:
                resNew.loc[Timeindex[i+4], "cycle"] = -1.5 * std[1]

        res.insert(res.shape[1], 'data', resNew["cycle"] + resNew["trend"])

        cycle1,trend1=sm.tsa.filters.hpfilter(res["data"],10)
        res.insert(res.shape[1], 'data1', trend1)

        data.loc[:, Allindex[col]] = res["data1"]

        # fig, ax = plt.subplots()
        # res[[Allindex[col],"data", "data1"]]["2001-03-31":].plot(ax=ax, fontsize=16);
        # plt.show()

    data.to_excel("newres.xlsx",index=False)



if __name__ == '__main__':

    data=readData()
    Timeindex,Allindex = generateIndex(data)
    HPFilter(data, Timeindex,Allindex)
